package com.lbj.mall.common.utils.excel;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.util.List;

/**
 * 导出Excel文件
 *  仅支持高版本,即扩展名是xlsx
 *
 *  用法：
 *     /export请求要排除api拦截(即能在浏览器直接访问): ApiExcludeRegisterCenter.register("/export");
 *
 *  @RequestMapping (value = "/export")
 * 	public void export(HttpServletRequest request, HttpServletResponse response) throws Exception {
 * 		response.setCharacterEncoding("utf-8");
 * 		response.setContentType("application/x-msdownload");
 * 		response.setHeader("Content-disposition","attachment; filename=" + new String("测试.xlsx".getBytes("utf-8"), "ISO8859-1")));
 *
 * 		String sheetName = "SheetName";
 * 		String header[] = new String[]{"姓名", "性别", "证件号码"};
 * 		List<String[]> dataList = new ArrayList<>();
 * 		dataList.add(new String[]{"张三", "男", "13512345678"});
 * 		ByteArrayOutputStream stream = new WriteExcel().create(sheetName, header, dataList);
 * 		stream.writeTo(response.getOutputStream());
 * 	}
 *
 */
public class WriteExcel {

    /**
     * 创建高版本Excel文件(即扩展名是xlsx)
     * @param sheetName 工作表名称
     * @param header 表头
     * @param dataList 数据(集合中数组长度和表头长度要一致)
     * @throws IOException
     */
    public ByteArrayOutputStream create(String sheetName, String[] header, List<String[]> dataList) throws IOException {
        if(StringUtils.isBlank(sheetName)){
            sheetName = "Sheet1";
        }
        //创建Excel文件薄
        XSSFWorkbook workbook = new XSSFWorkbook();
        //创建工作表sheeet
        Sheet sheet = workbook.createSheet(sheetName);

        //创建表头行
        int headerRow = 0;
        if(header != null) {
            headerRow = 1;

            XSSFCellStyle cellStyle = workbook.createCellStyle();
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

            Row title = sheet.createRow(0);
            for (int i = 0; i < header.length; i++) {
                Cell cell = title.createCell(i);
                cell.setCellValue(header[i]);
                cell.setCellStyle(cellStyle);
            }
        }

        //追加数据
        int size = dataList != null ? dataList.size() : 0;
        for (int i = 0; i < size; i++) {
            Row nextrow = sheet.createRow(i + headerRow);
            String[] data = dataList.get(i);
            for (int j = 0; j < data.length; j++) {
                Cell cell2 = nextrow.createCell(j);
                cell2.setCellValue(data[j]);
            }
        }

        //将表格写入ByteArrayOutputStream
        ByteArrayOutputStream stream = new ByteArrayOutputStream();
        workbook.write(stream);
        stream.close();
        return stream;
    }

}
